package softswitch.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import security.model.Corporation;
import security.model.Organization;
import security.service.SecurityService;
import softswitch.model.CallDetail;
import softswitch.model.Circuit;
import softswitch.model.Route;
import softswitch.model.RouteGroup;
import softswitch.ws.SoftswitchService;


@Stateless
public class CallDetailService {
	
	
	@PersistenceContext(unitName = "persistence")
    private EntityManager em;
	
	
	@EJB
	SoftswitchService softswitchService;
	
	@EJB
	RouteService routeService;
	
	@EJB
    SecurityService securityService;

	
	public CallDetail getById(Integer id){	
		return em.find(CallDetail.class, id);
	}
	
	public CallDetail save(CallDetail calldetail) {
    	em.persist(calldetail);
    	return calldetail;
    }
	
	public CallDetail update(CallDetail calldetail) {
    	return em.merge(calldetail);
    }
    
	public void delete(Integer id) {
    	em.remove(em.find(CallDetail.class, id));	
    }
    
    //----------------------------------------------------------------------

    public CallDetail getByUniqueId(String uniqueid) {
    	
    	String sql;
		sql = " SELECT e FROM CallDetail e WHERE e.uniqueid=:uniqueid  ";
		
		Query query = em.createQuery(sql);
		query.setParameter("uniqueid", uniqueid);
		
		try{
			return (CallDetail) query.getSingleResult();   
		}
		catch(Exception e){
			return null;
		}
		
    }

	public void deleteByMonth(Integer year, Integer month, Circuit circuit) {	
		String sql;
		sql = " DELETE FROM CallDetail e WHERE e.year=:year AND e.month=:month";
		
		if(circuit!=null){
			sql = sql + " AND e.circuit=:circuit ";
		}
		
		Query query = em.createQuery(sql);
		query.setParameter("year", year);
		query.setParameter("month", month);
		if(circuit!=null){
			query.setParameter("circuit", circuit);
		}   	
        query.executeUpdate();   
    }
	
	public void setProcessByMonth(Integer year, Integer month, Circuit circuit) {	
		    
		String sql = "UPDATE CallDetail e SET e.reprocess = true  WHERE e.year=:year AND e.month=:month";
		if(circuit!=null){
			sql = sql + " AND e.circuit=:circuit ";
		}
		Query query = em.createQuery(sql);
		query.setParameter("year", year);
		query.setParameter("month", month);
		if(circuit!=null){
			query.setParameter("circuit", circuit);
		}
        query.executeUpdate();  
    }

	@SuppressWarnings("unchecked")
	public List<CallDetail> listForProcess(Integer size) {
		Query query = em.createQuery("SELECT e FROM CallDetail e WHERE e.reprocess = true  ORDER BY answer ASC");
		query.setMaxResults(size);
		return query.getResultList();
	}  
	
	public List<CallDetail> listByCorporation(Corporation corporation, Date startdate, Date enddate, List<RouteGroup> routegroups, String disposition, String src, String dst, String order) {
    	
		System.out.println("listByCorporation");
		
    	List<CallDetail> result = new ArrayList<CallDetail>();
    	
        if (corporation == null) {
            
        	List<Corporation> corporations = securityService.listCorporations(0, 0);
        	Iterator<Corporation> corporationsIt = corporations.iterator();
        	while(corporationsIt.hasNext()){
        		
        		Corporation corporationTmp = corporationsIt.next();
        		
        		List<Organization> organizations = securityService.listOrganizationsByCorporation(corporationTmp);
        		Iterator<Organization> organizationsIt = organizations.iterator();
        		while(organizationsIt.hasNext()){
        			Organization organization = organizationsIt.next();
        			result.addAll(listByOrganization(organization, startdate, enddate, routegroups, disposition, src, dst, order));
        		}
        		
        	}
        	
        }
        
        else{
        	
        	List<Organization> organizations = securityService.listOrganizationsByCorporation(corporation);
    		Iterator<Organization> organizationsIt = organizations.iterator();
    		while(organizationsIt.hasNext()){
    			Organization organization = organizationsIt.next();
    			result.addAll(listByOrganization(organization, startdate, enddate, routegroups, disposition, src, dst, order));
    		}
        	
        }

        return result;
        
	}
	
	public List<CallDetail> listByOrganization(Organization organization, Date startdate, Date enddate, List<RouteGroup> routegroups, String disposition, String src, String dst, String order) {
    	
		System.out.println("listByOrganization");
		
    	List<CallDetail> result = new ArrayList<CallDetail>();
    	
        if (organization == null) {
        	List<Organization> organizations = securityService.listOrganizations(0, 0);
        	Iterator<Organization> organizationsIt = organizations.iterator();
        	while(organizationsIt.hasNext()){
        		
        		Organization organizationTmp = organizationsIt.next();
        		List<Circuit> circuits = softswitchService.listCircuitsByOrganizationId(organizationTmp.getId());
            	Iterator<Circuit> circuitsIt = circuits.iterator();
            	while(circuitsIt.hasNext()){
            		Circuit circuit = circuitsIt.next();
            		result.addAll(listByDateRange(circuit, startdate, enddate, routegroups, disposition, src, dst, order));
            	}
            	
        	}
        }
        else{
        	List<Circuit> circuits = softswitchService.listCircuitsByOrganizationId(organization.getId());
        	Iterator<Circuit> circuitsIt = circuits.iterator();
        	while(circuitsIt.hasNext()){
        		Circuit circuit = circuitsIt.next();
        		result.addAll(listByDateRange(circuit, startdate, enddate, routegroups, disposition, src, dst, order));
        	}
        }
        return result;        
	}
		
	@SuppressWarnings("unchecked")
	public List<CallDetail> listByMonth(Circuit circuit, Date calldate, List<RouteGroup> routegroups, String disposition, String src, String dst, String order) {

		System.out.println("listByCircuit");
		
		String sql;
		sql = " SELECT e FROM CallDetail e WHERE e.circuit=:circuit AND e.year=:year AND e.month=:month ";
		
		if(routegroups!=null){		
			String routeSql = "";
			Boolean first = true;
			for (RouteGroup routegroup : routegroups) {
				List<Route> routes = routeService.listByRouteGroup(routegroup);
				for (Route route : routes) {  
					if(first == true){
						routeSql = routeSql + " e.route.id = " + route.getId() ;
						first = false;
					}
					else{
						routeSql = routeSql + " OR e.route.id = " + route.getId() ;
					}		
				}				
			}
			if(!routeSql.isEmpty()){
				sql = sql + " AND (" + routeSql + " ) ";
			}
			else{
				return new ArrayList<CallDetail>();
			}
			
		}		
		if(disposition!=null){
			sql = sql + " AND e.disposition = '" + disposition + "'";
		}		
		if(src!=null){
			sql = sql + " AND e.src = '" + src + "'";
		}
		if(dst!=null){
			sql = sql + " AND e.dst = '" + dst + "'";
		}
		if(order!=null){
			if(order.equals("DESC")){
				sql = sql + " ORDER BY e.calldate DESC ";
			}
			if(order.equals("ASC")){
				sql = sql + " ORDER BY e.calldate ASC ";
			}
		}
		else{
			sql = sql + " ORDER BY e.calldate DESC ";
		}
		
		Query query = em.createQuery(sql);
		query.setParameter("circuit", circuit);
		query.setParameter("calldate", calldate);
		System.out.println("SQL: "+sql);
		
        return query.getResultList();   
	}

	@SuppressWarnings("unchecked")
	public List<CallDetail> listByDateRange(Circuit circuit, Date startdate, Date enddate, List<RouteGroup> routegroups, String disposition, String src, String dst, String order) {

		System.out.println("listByCircuit");
		
		String sql;
		sql = " SELECT e FROM CallDetail e WHERE e.circuit=:circuit AND  e.calldate BETWEEN :startdate AND :enddate ";
		
		if(routegroups!=null){		
			String routeSql = "";
			Boolean first = true;
			for (RouteGroup routegroup : routegroups) {
				List<Route> routes = routeService.listByRouteGroup(routegroup);
				for (Route route : routes) {  
					if(first == true){
						routeSql = routeSql + " e.route.id = " + route.getId() ;
						first = false;
					}
					else{
						routeSql = routeSql + " OR e.route.id = " + route.getId() ;
					}		
				}				
			}
			if(!routeSql.isEmpty()){
				sql = sql + " AND (" + routeSql + " ) ";
			}
			else{
				return new ArrayList<CallDetail>();
			}
			
		}		
		if(disposition!=null){
			sql = sql + " AND e.disposition = '" + disposition + "'";
		}		
		if(src!=null){
			sql = sql + " AND e.src = '" + src + "'";
		}
		if(dst!=null){
			sql = sql + " AND e.dst = '" + dst + "'";
		}
		if(order!=null){
			if(order.equals("DESC")){
				sql = sql + " ORDER BY e.calldate DESC ";
			}
			if(order.equals("ASC")){
				sql = sql + " ORDER BY e.calldate ASC ";
			}
		}
		else{
			sql = sql + " ORDER BY e.calldate DESC ";
		}
		
		Query query = em.createQuery(sql);
		query.setParameter("circuit", circuit);
		query.setParameter("startdate", startdate);
		query.setParameter("enddate", enddate);
		
		System.out.println("SQL: "+sql);
		
        return query.getResultList();   
	}

	
	
}
